package sspersonserver.db;
import sspersonserver.LoginData;
import sspersonserver.subjects.*;
import java.sql.*;
import java.util.ArrayList;

/**
 * Created by IntelliJ IDEA.
 * User: Double
 * Date: 30.01.12
 * Time: 14:34
 * To change this template use File | Settings | File Templates.
 */
public class MySQLDB implements IDB
{

	private Connection conn;
	private ResultSet rs;
	private Statement stmt;
	private ArrayList<Person> persons;
	private ArrayList<Country> countries;
	private ArrayList<City> cities;
	private ArrayList<Street> streets;
	private ArrayList<PhoneType> phoneTypes;


	private void execute(String sql)
	{
		try
		{
			stmt = conn.createStatement();
			stmt.executeQuery(sql);
		} catch (SQLException e)
		{
			e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
		}

	}
	private void executeUpdate(String sql)
	{
		try
		{
			stmt = conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e)
		{
			e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
		}

	}
	@Override
	public ArrayList<Person> getPersonList()
	{
		String sql = "Select * from person_short;";
		persons = new ArrayList<Person>();
		try
		{
			stmt = conn.createStatement();
			//stmt.execute(sql);
			
			if(stmt.execute(sql)){
				rs = stmt.getResultSet();
				while(rs.next())
				{
					persons.add(new Person(Integer.parseInt(rs.getString("id_p")),rs.getString("firstName"),rs.getString("lastName"),rs.getString("birthdate")));

				}
			}
			
		} catch (SQLException e)
		{
			e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
		}
		return persons;
	}

	@Override
	public ArrayList<Person> getPersonList(int startIndex, int endIndex)
	{
		System.out.println("MySQL getPersonList()");
		String sql = "Select *\n" +
				"from person_short\n" +
				"WHERE\n" +
				"id_p > "+startIndex+" AND id_p<="+endIndex+";";
		persons = new ArrayList<Person>();
		try
		{
			stmt = conn.createStatement();
			if(stmt.execute(sql)){
				rs = stmt.getResultSet();
				while(rs.next())
				{
					persons.add(new Person(Integer.parseInt(rs.getString("id_p")),rs.getString("firstName"),rs.getString("lastName"),rs.getString("birthdate")));

				}
			}

		} catch (SQLException e)
		{
			e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
		}
		return persons;
	}

	@Override
	public ArrayList<Country> getCountry()
	{
		String sql = "Select * from country;";
		countries = new ArrayList<Country>();
		try
		{
			stmt = conn.createStatement();
			//stmt.execute(sql);

			if(stmt.execute(sql)){
				rs = stmt.getResultSet();
				while(rs.next())
				{
					countries.add(new Country(Integer.parseInt(rs.getString("id")),rs.getString("country")));
				}
			}

		} catch (SQLException e)
		{
			e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
		}
		return countries;
	}

	@Override
	public ArrayList<City> getCity(int id)
	{
		String sql = "Select * from city\n" +
				"Where fk_countryid = "+id+";";
		cities = new ArrayList<City>();
		try
		{
			stmt = conn.createStatement();
			//stmt.execute(sql);

			if(stmt.execute(sql)){
				rs = stmt.getResultSet();
				while(rs.next())
				{
					cities.add(new City(Integer.parseInt(rs.getString("id")),rs.getString("city"),Integer.parseInt(rs.getString("fk_countryid"))));
				}
			}

		} catch (SQLException e)
		{
			e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
		}
		return cities;
	}

	@Override
	public ArrayList<Street> getStreet(int id)
	{
		String sql = "Select * from street\n" +
				"Where fk_cityid = "+id+";";
		streets = new ArrayList<Street>();
		try
		{
			stmt = conn.createStatement();
			//stmt.execute(sql);

			if(stmt.execute(sql)){
				rs = stmt.getResultSet();
				while(rs.next())
				{
					streets.add(new Street(Integer.parseInt(rs.getString("id")),rs.getString("street"),Integer.parseInt(rs.getString("fk_cityid"))));
				}
			}

		} catch (SQLException e)
		{
			e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
		}
		return streets;
	}

	@Override
	public ArrayList<Address> getAddress()
	{
		return null;  //To change body of implemented methods use File | Settings | File Templates.
	}

	@Override
	public void addCountry(Country country)
	{
		String sql = "Insert into country\n" +
				"(country)\n" +
				"VALUES (\""+country.getName()+"\");";
		executeUpdate(sql);
	}

	@Override
	public void addCity(City city)
	{
		String sql = "Insert into city\n" +
				"(city)\n" +
				"VALUES (\""+city.getName()+"\");";
		executeUpdate(sql);
	}

	@Override
	public void addStreet(Street street)
	{
		String sql = "Insert into street\n" +
				"(country)\n" +
				"VALUES (\""+street.getName()+"\");";
		executeUpdate(sql);
	}

	@Override
	public void removeCountry(Country country)
	{
		String sql = "Delete from country\n" +
				"where country = '"+country.getName()+"';";
		execute(sql);
	}

	@Override
	public void removeCity(City city)
	{
		String sql = "Delete from city\n" +
				"where city = '"+city.getName()+"';";
		execute(sql);
	}

	@Override
	public void removeStreet(Street street)
	{
		String sql = "Delete from street\n" +
				"where street = '"+street.getName()+"';";
		execute(sql);
	}
	public PhoneType getPhoneTypeById(int id)
	{
		String sql = "Select *\n" +
				"from phonetype\n" +
				"WHERE\n" +
				"id = "+id+";";
		PhoneType phoneType = null;
		try
		{
			stmt = conn.createStatement();
			if(stmt.execute(sql)){
				rs = stmt.getResultSet();
				while(rs.next())
				{
					phoneType = new PhoneType(Integer.parseInt(rs.getString("id")),rs.getString("type"));

				}
			}

		} catch (SQLException e)
		{
			e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
		}
		return phoneType;
	}
	public ArrayList<Phone> getPhonesByPersonId(int id)
	{
		ArrayList<Phone> tempPhones = new ArrayList<Phone>();
		String sql = "Select *\n" +
				"from phones\n" +
				"WHERE\n" +
				"id_owner = "+id+";";
		try
		{
			stmt = conn.createStatement();
			if(stmt.execute(sql)){
				rs = stmt.getResultSet();
				while(rs.next())
				{
					tempPhones.add(new Phone(Integer.parseInt(rs.getString("id")),rs.getString("phone"),getPhoneTypeById(Integer.parseInt(rs.getString("id_type")))));
				}
			}

		} catch (SQLException e)
		{
			e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
		}
		return tempPhones;
	}
	public ArrayList<Address> getAddressesByPersonId(int id)
	{
		ArrayList<Address> tempAddresses = new ArrayList<Address>();
		String sql = "Select *\n" +
				"from address\n" +
				"WHERE\n" +
				"fk_pid = "+id+";";
		try
		{
			stmt = conn.createStatement();
			if(stmt.execute(sql)){
				rs = stmt.getResultSet();
				while(rs.next())
				{
					int tempid = Integer.parseInt(rs.getString("id"));
					int fk_pid = Integer.parseInt(rs.getString("fk_pid"));
					String build = rs.getString("build");
					Street street = getStreetById(Integer.parseInt(rs.getString("fk_streetid")));
					City city = getCityById(street.getFk_id());
					Country country = getCountryById(city.getFk_id());
					tempAddresses.add(new Address(tempid,fk_pid,city,country,street,build));
				}
			}

		} catch (SQLException e)
		{
			e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
		}
		return tempAddresses;
	}
	public Country getCountryById(int id)
	{
		String sql = "Select *\n" +
				"from country\n" +
				"WHERE\n" +
				"id = "+id+";";
		Country tempCountry=null;
		try
		{
			stmt = conn.createStatement();
			if(stmt.execute(sql)){
				rs = stmt.getResultSet();
				while(rs.next())
				{
					tempCountry = new Country(Integer.parseInt(rs.getString("id")),rs.getString("country"));
				}
			}

		} catch (SQLException e)
		{
			e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
		}
		return tempCountry;
	}
	public City getCityById(int id)
	{
		String sql = "Select *\n" +
				"from city\n" +
				"WHERE\n" +
				"id = "+id+";";
		City tempCity=null;
		try
		{
			stmt = conn.createStatement();
			if(stmt.execute(sql)){
				rs = stmt.getResultSet();
				while(rs.next())
				{
					tempCity = new City(Integer.parseInt(rs.getString("id")),rs.getString("city"),Integer.parseInt(rs.getString("fk_countryid")));
				}
			}

		} catch (SQLException e)
		{
			e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
		}
		return tempCity;
	}
	public Street getStreetById(int id)
	{
		String sql = "Select *\n" +
				"from street\n" +
				"WHERE\n" +
				"id = "+id+";";
		Street tempStreet=null;
		try
		{
			stmt = conn.createStatement();
			if(stmt.execute(sql)){
				rs = stmt.getResultSet();
				while(rs.next())
				{
					tempStreet = new Street(Integer.parseInt(rs.getString("id")),rs.getString("street"),Integer.parseInt(rs.getString("fk_cityid")));
				}
			}

		} catch (SQLException e)
		{
			e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
		}
		return tempStreet;
	}
	
	public String getCVByPersonId(int id)
	{

		String sql = "Select *\n" +
				"from cvs\n" +
				"WHERE\n" +
				"id = "+id+";";
		String tempCV=null;
		try
		{
			stmt = conn.createStatement();
			if(stmt.execute(sql)){
				rs = stmt.getResultSet();
				while(rs.next())
				{
					tempCV = rs.getString("cv");
					System.out.println("Users CV:"+tempCV);
				}
			}

		} catch (SQLException e)
		{
			e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
		}
		return tempCV;	
	}
	public byte[] getPhotoByPersonId(int id)
	{
		return null;	
	}
	public Person getPersonById(int id)
	{
		System.out.println("MySQL getPersonById()");
		Person p = null;
		String sql = "Select *\n" +
				"from person_short\n" +
				"WHERE\n" +
				"id_p = "+id+";";
		try
		{
			stmt = conn.createStatement();
			if(stmt.execute(sql)){
				rs = stmt.getResultSet();
				while(rs.next())
				{
					p = new Person(Integer.parseInt(rs.getString("id_p")),rs.getString("firstName"),rs.getString("lastName"),rs.getString("birthdate"));
				}
			}

		} catch (SQLException e)
		{
			e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
		}
		return p.clone();
	}

	@Override
	public FullPerson getFullPerson(int id)
	{
		Person p;
		FullPerson fullPerson = null;
		ArrayList<Address> tempAddr;
		ArrayList<Phone> tempPhones;
		byte[] photo;
		String cv;

		p=getPersonById(id);
		tempAddr = getAddressesByPersonId(id);
		tempPhones = getPhonesByPersonId(id);
		photo = getPhotoByPersonId(id);
		cv = getCVByPersonId(id);
		fullPerson = new FullPerson(p,tempPhones,tempAddr,photo,cv);


		return fullPerson;  //To change body of implemented methods use File | Settings | File Templates.
	}

	@Override
	public void saveFullPerson(FullPerson fullPerson)
	{
		String firstName = fullPerson.getPerson().getFirstName();
		String lastName = fullPerson.getPerson().getLastName();
		String birthdate = fullPerson.getPerson().getBirthDate();

		String sql = "Insert into person_short\n" +
				"(firstName,lastName,birthdate)\n" +
				"VALUES\n" +
				"(\""+firstName+"\",\""+lastName+"\",\""+birthdate+"\");";
		System.out.println(sql);
		executeUpdate(sql);
		
		int id = getIdofPerson(fullPerson.getPerson().getFirstName(),fullPerson.getPerson().getLastName(),fullPerson.getPerson().getBirthDate());
		for(Phone phone:fullPerson.getPhones())
		{
			phone.setOwnerId(id);
		}
		for(Address addr:fullPerson.getAddress())
		{
			addr.setOwnerid(id);
		}
		addPersonsPhones(fullPerson.getPhones());
		addPersonsAddresses(fullPerson.getAddress());
		addPersonsCV(id,fullPerson.getCv());
		
	}
	private void addPersonsPhones(ArrayList<Phone> phones)
	{
		for(Phone phone:phones)
		{
			String sql = "Insert into phones\n" +
					"(owner_id,phone,id_type)\n" +
					"VALUES ("+phone.getOwnerId()+",\""+
					phone.getNum()+"\","+phone.getType().getId()+");";
			executeUpdate(sql);
		}
		
	}
	private void addPersonsAddresses(ArrayList<Address> addresses)
	{
		for(Address addr:addresses)
		{
			String sql = "Insert into address\n" +
					"(fk_pid,fk_streetid,build)\n" +
					"VALUES ("+addr.getOwnerid()+","+
					addr.getStreet().getId()+",\""+addr.getBuild()+"\");";
			executeUpdate(sql);
		}
	}
	private void addPersonsCV(int id, String cv)
	{
		String sql = "Insert into cvs\n" +
				"(fk_pid,cv)\n" +
				"VALUES ("+id+",\""+cv+"\");";
		executeUpdate(sql);
	}
	private int getIdofPerson(String fName,String lName,String birthDate)
	{
		int id = 0;
		String sql = "Select * from person_short\n" +
				"Where\n" +
				"firstName = \""+fName+"\"\n" +
				"AND\n" +
				"lastName = \""+lName+"\"\n" +
				"AND\n" +
				"birthdate = \""+birthDate+"\";";
		try
		{
			stmt = conn.createStatement();
			if(stmt.execute(sql)){
				rs = stmt.getResultSet();
				while(rs.next())
				{
					id = Integer.parseInt(rs.getString("id_p"));
				}
			}

		} catch (SQLException e)
		{
			e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
		}

		return id;
	}
	@Override
	public void save(ArrayList<Person> persons)
	{
		//To change body of implemented methods use File | Settings | File Templates.
	}

	@Override
	public void update(FullPerson fullPerson)
	{
		//To change body of implemented methods use File | Settings | File Templates.
	}

	@Override
	public void delete(FullPerson fullPerson)
	{
		//To change body of implemented methods use File | Settings | File Templates.
	}

	@Override
	public int checkAuth(LoginData loginData)
	{

		String sql = "Select * from users;";
		int authState=-1;
		try
		{
			stmt = conn.createStatement();
			//stmt.execute(sql);

			if(stmt.execute(sql)){
				rs = stmt.getResultSet();
				while(rs.next())
				{
					authState = Integer.parseInt(rs.getString("role"));
				}
			}

		} catch (SQLException e)
		{
			e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
		}


		return authState;
	}

	@Override
	public void connect(String ip, String database)
	{
		try {
			conn = DriverManager.getConnection("jdbc:mysql://"+ip+"/"+database+"?" +
					"user=root&password=root");

			System.out.println("Connected.");
		} catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}
	}

	@Override
	public void disconnect()
	{
		try
		{
			conn.close();
		} catch (SQLException e)
		{
			e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
		}
	}

	@Override
	public ArrayList<PhoneType> getPhoneType()
	{
		String sql = "Select * from phonetype;";
		phoneTypes = new ArrayList<PhoneType>();
		try
		{
			stmt = conn.createStatement();
			//stmt.execute(sql);

			if(stmt.execute(sql)){
				rs = stmt.getResultSet();
				while(rs.next())
				{
					phoneTypes.add(new PhoneType(Integer.parseInt(rs.getString("id")), rs.getString("type")));
				}
			}

		} catch (SQLException e)
		{
			e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
		}
		return phoneTypes;
	}

	@Override
	public void addPhoneType(PhoneType phoneType)
	{
		String sql = "Insert into phonetype\n" +
				"(type)\n" +
				"VALUES (\""+phoneType.getName()+"\");";
		executeUpdate(sql);
	}

	@Override
	public void removePhoneType(PhoneType phoneType)
	{
		String sql = "Delete from phonetype\n" +
				"where type = '"+phoneType.getName()+"';";
		execute(sql);
	}
}
